package bit.study;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
//JDBC --> myBatis
public class MemDAO {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	public boolean memInsert(MemDTO dto) {
		conn = Connect.getConnection();
		String sql = "insert into tbladdr values(addr_num.nextVal,?,?,?,?)";
		boolean succ=false;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getId());
			ps.setString(2, dto.getName());
			ps.setString(3, dto.getAddr());
			ps.setString(4, dto.getTel());
			int cnt = ps.executeUpdate();
			if(cnt>0)
				succ=true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return succ;
	}
	
	//public ArrayList<MemDTO> memAllList()
	public ArrayList<MemDTO> memAllList(){
		ArrayList<MemDTO> list = new ArrayList<MemDTO>();
		conn = Connect.getConnection();
		String sql = "select * from tbladdr";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while(rs.next()){
				MemDTO dto = new MemDTO();
				dto.setNum(rs.getInt("num"));
				dto.setId(rs.getString("id"));
				dto.setName(rs.getString("name"));
				dto.setAddr(rs.getString("addr"));
				dto.setTel(rs.getString("tel"));
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public boolean memDelete(int num) {
		conn = Connect.getConnection();
		String sql = "delete from tbladdr where num=?";
		
		boolean succ=false;
		try{
			ps = conn.prepareStatement(sql);
			ps.setInt(1, num);
			int cnt = ps.executeUpdate();
			if(cnt>0) succ=true;
		}catch(Exception e){
			e.printStackTrace();
		}
		return succ;
	}

	public MemDTO selectByNum(int num) {
		MemDTO dto = null;
		conn = Connect.getConnection();
		String sql = "select * from tbladdr where num=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, num);
			rs = ps.executeQuery();
			if(rs.next()){
				String id = rs.getString("id");
				String name = rs.getString("name");
				String addr = rs.getString("addr");
				String tel = rs.getString("tel");
				dto = new MemDTO(num, id, name, addr, tel);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		return dto;
	}

	public boolean update(MemDTO dto) {
		conn = Connect.getConnection();
		String sql = "update tbladdr set id=?, name=?, addr=?, tel=? where num=?";
		boolean succ = false;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getId());
			ps.setString(2, dto.getName());
			ps.setString(3, dto.getAddr());
			ps.setString(4, dto.getTel());
			ps.setInt(5, dto.getNum());
			int cnt = ps.executeUpdate();
			if(cnt>0)
				succ = true;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return succ;
	}

	public ArrayList<MemDTO> memSearchList(String part, String select) {
		ArrayList<MemDTO> list = new ArrayList<MemDTO>();
		conn = Connect.getConnection();
		String sql = "select * from tbladdr where "+part+" like ?";
		System.out.println(sql);
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+select+"%");
			rs = ps.executeQuery();
			while(rs.next()){
				int num = rs.getInt("num");
				String id = rs.getString("id");
				String name = rs.getString("name");
				String addr = rs.getString("addr");
				String tel = rs.getString("tel");
				MemDTO dto = new MemDTO(num, id, name, addr, tel);
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}
